import ExcelJS from 'exceljs';
import { Readable } from 'stream';

/**
 * Excel工具类
 * 用于后端处理导入导出
 */

export interface ExcelColumn {
  header: string;
  key: string;
  width?: number;
}

export interface ExportExcelOptions {
  filename: string;
  sheetName?: string;
  columns: ExcelColumn[];
  data: any[];
}

export interface ImportExcelResult<T = any> {
  success: boolean;
  data?: T[];
  errors?: string[];
  total?: number;
}

/**
 * 导出数据到Excel
 */
export async function exportToExcel(options: ExportExcelOptions): Promise<Buffer> {
  const { sheetName = 'Sheet1', columns, data } = options;

  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet(sheetName);

  // 设置列
  worksheet.columns = columns.map(col => ({
    header: col.header,
    key: col.key,
    width: col.width || 15,
  }));

  // 设置表头样式
  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE0E0E0' },
  };

  // 添加数据
  data.forEach(item => {
    worksheet.addRow(item);
  });

  // 生成Buffer
  const buffer = await workbook.xlsx.writeBuffer();
  return buffer as any;
}

/**
 * 从Excel导入数据
 */
export async function importFromExcel<T = any>(
  fileBuffer: Buffer,
  validator?: (row: any, rowNumber: number) => { valid: boolean; errors?: string[] }
): Promise<ImportExcelResult<T>> {
  try {
    const workbook = new ExcelJS.Workbook();
    await workbook.xlsx.load(fileBuffer as any);

    const worksheet = workbook.worksheets[0];
    if (!worksheet) {
      return {
        success: false,
        errors: ['Excel文件为空'],
      };
    }

    const data: T[] = [];
    const errors: string[] = [];
    let headerRow: any = null;

    worksheet.eachRow((row, rowNumber) => {
      if (rowNumber === 1) {
        // 第一行是表头
        headerRow = row.values;
        return;
      }

      const rowData: any = {};
      row.eachCell((cell, colNumber) => {
        const header = headerRow[colNumber];
        if (header) {
          rowData[header] = cell.value;
        }
      });

      // 验证数据
      if (validator) {
        const validation = validator(rowData, rowNumber);
        if (!validation.valid) {
          errors.push(`第${rowNumber}行: ${validation.errors?.join(', ')}`);
          return;
        }
      }

      data.push(rowData);
    });

    if (errors.length > 0) {
      return {
        success: false,
        errors,
        total: worksheet.rowCount - 1,
      };
    }

    return {
      success: true,
      data,
      total: data.length,
    };
  } catch (error: any) {
    return {
      success: false,
      errors: [`Excel解析失败: ${error.message}`],
    };
  }
}

/**
 * 生成Excel模板
 */
export async function generateTemplate(columns: ExcelColumn[], sampleData?: any[]): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  const worksheet = workbook.addWorksheet('Template');

  // 设置列
  worksheet.columns = columns.map(col => ({
    header: col.header,
    key: col.key,
    width: col.width || 15,
  }));

  // 设置表头样式
  worksheet.getRow(1).font = { bold: true };
  worksheet.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE0E0E0' },
  };

  // 添加示例数据
  if (sampleData && sampleData.length > 0) {
    sampleData.forEach(item => {
      worksheet.addRow(item);
    });
  }

  // 生成Buffer
  const buffer = await workbook.xlsx.writeBuffer();
  return buffer as any;
}

/**
 * 将Buffer转换为Stream（用于Koa响应）
 */
export function bufferToStream(buffer: Buffer): Readable {
  const stream = new Readable();
  stream.push(buffer);
  stream.push(null);
  return stream;
}

